<?php
/**
 * export excel
 *
 * @param array $worksheets
 * @param string $file_name
 */
function fw_export_excel2($worksheets,$file_name){ //with array
	#write to excel file
	#Include PEAR::Spreadsheet_Excel_Writer
	require_once PATH . "tools/Spreadsheet/Excel/Writer.php";
	$file = 'files/cache/'.$file_name.date('Hi').'.xls';
	$xls = new Spreadsheet_Excel_Writer($file);

	#set file name
//	$xls->send($file_name.".xls");
	$xls->setVersion(8,'utf-8');

	#Add a worksheet to the file, returning an object to add data to
	#Set format cell & font
	$format_header = $xls->addFormat();
	$format_header->setBold();
	$format_header->setFontFamily('Tahoma');
	$format_header->setSize('7');
	$format_header->setAlign('center');
	$format_header->setTextWrap();
	$format_header->setVAlign('top');

	$format_header_center = $xls->addFormat();
	$format_header_center->setBold();
	$format_header_center->setFontFamily('Tahoma');
	$format_header_center->setSize('7');
	$format_header_center->setAlign('center');
	$format_header_center->setTextWrap();
	$format_header_center->setVAlign('middle');

	$format_data = $xls->addFormat();
	$format_data->setFontFamily('Tahoma');
	$format_data->setSize('7');
	$format_data->setAlign('left');
	$format_data->setTextWrap();
	$format_data->setVAlign('top');

	$format_data_right = $xls->addFormat();
	$format_data_right->setFontFamily('Tahoma');
	$format_data_right->setSize('7');
	$format_data_right->setAlign('right');
	$format_data_right->setTextWrap();
	$format_data_right->setVAlign('top');

	$format_data_right10 = $xls->addFormat();
	$format_data_right10->setFontFamily('Tahoma');
	$format_data_right10->setSize('10');
	$format_data_right10->setAlign('right');
	$format_data_right10->setTextWrap();
	$format_data_right10->setVAlign('top');

	$format_data_center = $xls->addFormat();
	$format_data_center->setFontFamily('Tahoma');
	$format_data_center->setSize('7');
	$format_data_center->setAlign('center');
	$format_data_center->setTextWrap();
	$format_data_center->setVAlign('top');

	$format_money = $xls->addFormat();
	$format_money->setSize('7');
	$format_money->setTextWrap();
	$format_money->setFontFamily('Tahoma');
	$format_money->setAlign('right');
	$format_money->setVAlign('top');
	$format_money->setNumFormat('#,###.00');

	#for each sheets(multi sheets)
	foreach ($worksheets as $sheets_name => $data) {
		$sheet =& $xls->addWorksheet($sheets_name);
		$sheet->setInputEncoding('utf-8');
		#set page
		if (is_array($data['set_page'])) {
			foreach ($data['set_page'] as $k => $v) {
				if ($k == 'setLandscape') {
					$sheet->setLandscape();
				}elseif ($k == 'setPaper') {
					$sheet->setPaper($v);
				}elseif ($k == 'setHeader') {
					$set_Header = $v;
				}elseif ($k == 'setFooter') {
					$sheet->setFooter($v);
				}
				#TODO add more ...
			}
		}
		if (isset($data['sub_header'])) {
			$set_subHeader = '&L';
			foreach ($data['sub_header'] as $k => $v) {
				$set_subHeader .= ' '.$v.' ';
			}
		}
		#set default setting page
		$sheet->setHeader($set_subHeader.$set_Header.'&RDate: &D');
		$sheet->setFooter('&C&P/&N');
		//$sheet->centerHorizontally();
		$sheet->setMargins_LR('0.5');
		#set width of column
		if (isset($data['column_width'])) {
			foreach ($data['column_width'] as $k => $v) {
				if ($v['stop'] == '') $v['stop'] = $v['start'];
				$sheet->setColumn($v['start'],$v['stop'],$v['width']);
			}
		}
		$i = 0;
		$j = 0;
		$b = 0;
		#show column header
		if ($data['head']) {
			foreach ($data['head'] as $k => $v) {
				$sheet->write($j,$i+$k,$v,$format_header);
			}
			$b = count($data['head']);
		}
		#show data
		if (isset($data['data'])) {
			foreach ($data['data'] as $k => $v) {
				++$j;
				$i = 0;
				foreach ($v as $m => $n) {
					$list = 1;
					if ($data['align'][$m] == 'C') $cell_style = $format_data_center;
					elseif ($data['align'][$m] == 'R') $cell_style = $format_data_right;
					elseif ($data['align'][$m] == 'M') {
						$list = 2;
						$cell_style = $format_money;
					}else $cell_style = $format_data;
					if ($list == 2) $sheet->writeNumber($j,$i+$m,$n,$cell_style);
					else $sheet->write($j,$i+$m,$n,$cell_style);
				}
			}
		}
	}
	$xls->close();
	#output
	header('Location: '.$file);
	exit;
}

function skipColumn($skip,$col,$row){
	if ($skip[$row][$col] == 1) {
		$col++;
		$col = skipColumn($skip,$col,$row);
	}
	return $col;
}

/**
 * return load excel file dialog
 * add more 1 sheet :: $table is array key->sheet name value->table html
 * parameter >> sheet_name, table: html table, header: show name of tab, sub_header: show search word
 * 		$width = array(
			array('start' => 0,'stop' => 1,'width' => 15),
			array('start' => 2,'stop' => 23,'width' => 18),
		);
		$table = array(
			'sheet_name' => array(
				'table' => $t,
				'header' => msg('report').': '.msg($_REQUEST['view']),
				'sub_header' => $s['header_arr'],
				'column_width' => $width
			)
		);
		fw_export_excel($table,'file_name');
 **** special class ****
 * class="txt_rotation" - for setTextRotaion [<td class="txt_rotation">txt_rotation</td>] >>do we need angle parameter
 * <center>text</center> - for setAlign('center') [<td><center>txt center</center></td>]
 */
function fw_export_excel($data,$file_name){ //with html table
	#write to excel file
	#Include PEAR::Spreadsheet_Excel_Writer
	require_once "tools/Spreadsheet/Excel/Writer.php";
	$file = 'files/cache/'.$file_name.date('Hi').'.xls';
	$xls = new Spreadsheet_Excel_Writer($file);
	//$xls->send($file_name.".xls");
	$xls->setVersion(8,'utf-8');
	#Set format cell
	$format_topic = $xls->addFormat();
	$format_topic->setFontFamily('Tahoma');
	$format_topic->setSize('20');
	$format_topic->setBold();

	$format_header = $xls->addFormat();
	$format_header->setBold();
	$format_header->setFontFamily('Tahoma');
	$format_header->setSize('7');
	$format_header->setAlign('center');
	$format_header->setTextWrap();
	$format_header->setVAlign('top');

	$format_header_center_rotation = $xls->addFormat();
	$format_header_center_rotation->setBold();
	$format_header_center_rotation->setFontFamily('Tahoma');
	$format_header_center_rotation->setSize('7');
	$format_header_center_rotation->setTextRotation(270);
	$format_header_center_rotation->setAlign('center');

	$format_data_rotation = $xls->addFormat();
	$format_data_rotation->setSize('7');
	$format_data_rotation->setFontFamily('Tahoma');
	$format_data_rotation->setTextRotation(270);

	foreach ($data as $sheets_name => $val) {
		#Add a worksheet to the file, returning an object to add data to
		$sheet =& $xls->addWorksheet($sheets_name);
		$sheet->setInputEncoding('utf-8');
		if (is_array($val['set_page'])) {
			foreach ($val['set_page'] as $k => $v) {
				if ($k == 'setLandscape') {
					$sheet->setLandscape();
				}elseif ($k == 'setPaper') {
					$sheet->setPaper($v);
				}elseif ($k == 'setHeader') {
					$set_Header = $v;
				}elseif ($k == 'setFooter') {
					$sheet->setFooter($v);
				}
				#TODO add more ...
			}
		}
		if (is_array($val['sub_header'])) {
			$set_subHeader = '&L';
			foreach ($val['sub_header'] as $k => $v) {
				$set_subHeader .= ' '.$v.' ';
			}
		}
		#default setting page
		$sheet->setHeader($set_subHeader.$set_Header.'&RDate: &D');
		#default setting page
		$sheet->setFooter('&C&P/&N');
		//$sheet->centerHorizontally();
		//$sheet->setMargins_LR(0);
		if (isset($val['row_height'])) {
			foreach ($val['row_height'] as $k => $v) {
				$row = explode('-',$v['row']);
				foreach ($row as $m => $n) {
					$sheet->setRow($n,$v['height']);
				}
			}
		}

		if (isset($val['column_width'])) {
			foreach ($val['column_width'] as $k => $v) {
				$sheet->setColumn($v['start'],$v['stop'],$v['width']);
			}
		}

		$j = 0;
		$tab = explode('<tr', $val['table']);
		unset($tab[0]);
		$regCell = '!<td([a-zA-Z0-9_ \"=]*)>(.*)</td>!Usi';
		$regColSpan = 'colspan="([0-9]+)"';
		$regRowSpan = 'rowspan="([0-9]+)"';
		$regClass = 'class="([a-zA-Z_]+)"';
		$regColor = 'color="([a-zA-Z_]+)"';

		$row = $j;
		$skip = array();
		foreach ($tab as $n => $l) {
			$col = 0;
			$styleH = 0;
			if (strstr($l,'<th')) {
				$l = str_replace('<th', '<td', $l);
				$l = str_replace('</th', '</td', $l);
				$styleH = 1;
			}

			preg_match_all($regCell,$l,$res);
			unset($res[0]);
			if (count($res[2])>0) {
				foreach ($res[2] as $x => $c) {
					$style = 0;
					$style_color = '';
					#check text center
					if (strstr($c,'<center>')) $style = 2;
					if (strstr($c,'<right>')) $style = 4;
					if (strstr($c,'<right10>')) $style = 7;
					if (strstr($c,'<formula_sum>')) $style = 6;
					if (strstr($c,'<money>')) $style = 5;

					#check text rotation
					$rotation = '';
					eregi($regClass,$res[1][$x],$rotation);
					if (strstr($rotation[1],'setTextRotaion')) $style = 3;

					#check text color
					$color = '';
					eregi($regColor,$res[1][$x],$color);
					if (strstr($color[1],'green')) $style_color = 'green';
					if (strstr($color[1],'black')) $style_color = 'black';
					if (strstr($color[1],'blue')) $style_color = 'blue';
					if (strstr($color[1],'red')) $style_color = 'red';

					#check for rowspan ($skip)
					$col = skipColumn($skip,$col,$row);
					#check for colspan and rowspan
					$colspan = '';
					$rowspan = '';
					eregi($regColSpan,$res[1][$x],$colspan);
					eregi($regRowSpan,$res[1][$x],$rowspan);
					###################
					if ($style_color) {
						#Set format cell
						$format_data = $xls->addFormat();
						$format_data->setSize('7');
						$format_data->setFontFamily('Tahoma');
						$format_data->setTextWrap();
						$format_data->setAlign('left');
						$format_data->setVAlign('top');
						$format_data->setColor($style_color);

						$format_data_center = $xls->addFormat();
						$format_data_center->setSize('7');
						$format_data_center->setFontFamily('Tahoma');
						$format_data_center->setTextWrap();
						$format_data_center->setAlign('center');
						$format_data_center->setVAlign('top');
						$format_data_center->setColor($style_color);

						$format_data_right = $xls->addFormat();
						$format_data_right->setSize('7');
						$format_data_right->setTextWrap();
						$format_data_right->setFontFamily('Tahoma');
						$format_data_right->setAlign('right');
						$format_data_right->setVAlign('top');
						$format_data_right->setColor($style_color);

						$format_formula_sum = $xls->addFormat();
						$format_formula_sum->setFontFamily('Tahoma');
						$format_formula_sum->setSize('10');
						$format_formula_sum->setAlign('right');
						$format_formula_sum->setTextWrap();
						$format_formula_sum->setVAlign('top');
						$format_formula_sum->setColor($style_color);

						$format_data_right10 = $xls->addFormat();
						$format_data_right10->setFontFamily('Tahoma');
						$format_data_right10->setSize('10');
						$format_data_right10->setAlign('right');
						$format_data_right10->setTextWrap();
						$format_data_right10->setVAlign('top');
						$format_data_right10->setColor($style_color);

						$format_money = $xls->addFormat();
						$format_money->setSize('7');
						$format_money->setTextWrap();
						$format_money->setFontFamily('Tahoma');
						$format_money->setAlign('right');
						$format_money->setVAlign('top');
						$format_money->setNumFormat('#,###.00');
						$format_money->setColor($style_color);
					} else {
						#Set format cell
						$format_data = $xls->addFormat();
						$format_data->setSize('7');
						$format_data->setFontFamily('Tahoma');
						$format_data->setTextWrap();
						$format_data->setAlign('left');
						$format_data->setVAlign('top');

						$format_data_center = $xls->addFormat();
						$format_data_center->setSize('7');
						$format_data_center->setFontFamily('Tahoma');
						$format_data_center->setTextWrap();
						$format_data_center->setAlign('center');
						$format_data_center->setVAlign('top');

						$format_data_right = $xls->addFormat();
						$format_data_right->setSize('7');
						$format_data_right->setTextWrap();
						$format_data_right->setFontFamily('Tahoma');
						$format_data_right->setAlign('right');
						$format_data_right->setVAlign('top');

						$format_formula_sum = $xls->addFormat();
						$format_formula_sum->setFontFamily('Tahoma');
						$format_formula_sum->setSize('10');
						$format_formula_sum->setAlign('right');
						$format_formula_sum->setTextWrap();
						$format_formula_sum->setVAlign('top');

						$format_data_right10 = $xls->addFormat();
						$format_data_right10->setFontFamily('Tahoma');
						$format_data_right10->setSize('10');
						$format_data_right10->setAlign('right');
						$format_data_right10->setTextWrap();
						$format_data_right10->setVAlign('top');

						$format_money = $xls->addFormat();
						$format_money->setSize('7');
						$format_money->setTextWrap();
						$format_money->setFontFamily('Tahoma');
						$format_money->setAlign('right');
						$format_money->setVAlign('top');
						$format_money->setNumFormat('#,###.00');
					}
					######
					#display cell with style
					if ($styleH == 1) {
						if ($style == 3) $cell_style = $format_header_center_rotation; #header bold text
						else $cell_style = $format_header; //
					}elseif ($style == 2) $cell_style = $format_data_center;
					elseif ($style == 3) $cell_style = $format_data_rotation;
					elseif ($style == 4) $cell_style = $format_data_right;
					elseif ($style == 5) $cell_style = $format_money;
					elseif ($style == 6) $cell_style = $format_money;
					elseif ($style == 7) $cell_style = $format_data_right10;
					else $cell_style = $format_data; # normal text

					#set more color
					//if ($style_color == 'red') $cell_style->setColor('red');

					$c = strip_tags($c);
					if ($style == 3) $c = ' '.$c;
					if ($style == 6) $sheet->writeFormula($row,$col,'=SUM(M3:M'.count($tab).')',$cell_style);
					if ($style == 5) $sheet->writeNumber($row,$col,$c,$cell_style);
					else $sheet->writeString($row,$col,$c,$cell_style);
					#TODO echo strip_tags($c).'|'.$row.'|'.$col.'<br/>';
					if ($rowspan[1]>1) {
						#do the rowspan
						for($i=1;$i<$rowspan[1];$i++) $skip[($row+$i)][$col] = 1;
						$sheet->mergeCells($row,$col,$row+$rowspan[1]-1,$col);
					}
					if ($colspan[1]>1) {
						#do colspan
						if ($rowspan[1]>1) {
							for($j=1;$j<$colspan[1];$j++) {
								for($i=1;$i<$rowspan[1];$i++) $skip[($row+$i)][$col+$j] = 1;
							}
							$sheet->mergeCells($row,$col,$row+$rowspan[1]-1,$col+$colspan[1]-1);
						}else $sheet->mergeCells($row,$col,$row,$col+$colspan[1]-1);
						$col += $colspan[1];
					}else $col++;
				}
			}
			$row++;
		}
	}
	$xls->close();
	#output
	header('Location: '.$file);
	exit;
}
?>